package repositorios;

import interfaceRepositorio.IRepositorioRegistroProfissional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import entidades.RegistroProfissional;
import excecoes.FalhaPersistenciaException;

public class RepositorioRegistroProfissional extends RepositorioAbstrato implements IRepositorioRegistroProfissional {

	private String cadastraSTM = "INSERT INTO TBREGISTRO (TBServidor_cpf, numero, uf, orgao, regiao) "
			+ "VALUES (?, ?, ?, ?, ?)";

	private String consultaSTM = "SELECT * FROM TBREGISTRO WHERE TBServidor_cpf = ?";

	private String atualizaSTM = " UPDATE TBREGISTRO SET numero = ?,  uf = ?, orgao = ?, regiao = ? "
			+ "WHERE TBServidor_cpf = ?";

	public RepositorioRegistroProfissional() {
		super();
	}

	/**
	 * Cadastra registro profissional
	 * 
	 * @param registro
	 * @throws FalhaPersistenciaException 
	 * @throws SQLException 
	 */
	public void cadastrar(RegistroProfissional registro, String cpfServidor)
			throws FalhaPersistenciaException, SQLException {

		Connection conn = encontrarConexao();
		PreparedStatement stm = conn.prepareStatement(cadastraSTM);

		stm.setString(1, cpfServidor);
		stm.setInt(2, registro.getNumero());
		stm.setString(3, registro.getUf());
		stm.setString(4, registro.getOrgao());
		stm.setString(5, registro.getRegiao());

		stm.execute();	
		stm.close();
	}

	/**
	 * Consulta registro profisional
	 * 
	 * @param numero
	 * @return
	 * @throws FalhaPersistenciaException 
	 * @throws SQLException 
	 */
	public RegistroProfissional consultar(String cpfServidor)
			throws FalhaPersistenciaException, SQLException {

		RegistroProfissional registro = null;

		Connection conn = encontrarConexao();
		PreparedStatement stm = conn.prepareStatement(consultaSTM);

		stm.setString(1, cpfServidor);

		ResultSet result = stm.executeQuery();
		if (result.next()) {
			registro = new RegistroProfissional();
			
			registro.setNumero(result.getInt("numero"));
			registro.setUf(result.getString("uf"));
			registro.setOrgao(result.getString("orgao"));
			registro.setRegiao(result.getString("regiao"));
		}		
		stm.close();
		if(result!=null){
			result.close();
		}
		
		return registro;
	}

	public void atualizar(RegistroProfissional registro, String cpfServidor) throws SQLException,
			FalhaPersistenciaException {

		Connection conn = encontrarConexao();
		PreparedStatement stm = conn.prepareStatement(atualizaSTM);

		stm.setInt(1, registro.getNumero());
		stm.setString(2, registro.getUf());
		stm.setString(3, registro.getOrgao());
		stm.setString(4, registro.getRegiao());		
		stm.setString(5, cpfServidor);

		stm.execute();	
		stm.close();
	}

}
